Proactive Index Maintenance: Using Prediction Models for Improving Index Maintenance in Databases

This article presents a mechanism, denoted Proactive Index Maintenance (PIM, for short), for proactive index management based on the use of prediction models. The main objective of the proposed mechanism is to predict when a time-consuming query q will be executed, in order to proactively create index structures which reduce q′s response time. After q is executed, PIM drops the created indexes for avoiding the overhead of updating index structures. Thus, indexes are automatically created and dropped by PIM in a proactive manner. PIM is DBMS-independent, runs continuously and with no DBA intervention. Experiments show that PIM presents low overhead, can be effectively deployed to predict time-consuming query execution and provides significant performance gain during time-consuming query execution. Different prediction models have been evaluated: neural networks (Multi-Layer Perceptron MLP and Radial Basis Function RBF) and Linear Regression. The results indicate that the prediction model is query-specific, i.e., it should be defined according to the statistical distribution (normal, poisson, binomial) of the query execution history.